<%@ page language="java" contentType="text/javascript; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.util.Calendar"%>
<%@ page import="java.util.Date"%>
<%@ page import="java.text.DecimalFormat"%>
<%@ page import="java.text.SimpleDateFormat"%>
<%@page import='java.sql.*' %>
<%@page import="java.util.ArrayList"%>
<%@page import="net.sf.json.JSONObject"%>
<%@page import="net.sf.json.JSONSerializer"%>
<%@page import="net.sf.json.JSONArray"%>

<%String callback = request.getParameter("callback");%>
<%=callback %> (<%

	String continent = request.getParameter("continent");
	String nation = request.getParameter("nation");
	
	String p = request.getParameter("pages"); 
    String rpp = request.getParameter("rpp");
    String start = request.getParameter("start");
    int rpps = Integer.parseInt(rpp);
    int starts = Integer.parseInt(start);
	
	JSONObject jo = new JSONObject();
	JSONObject jo2 = new JSONObject();
    JSONArray ja = new JSONArray();
	
	Connection conn = null;
	PreparedStatement pstmt = null;	
	
	try{
		Class.forName("com.mysql.jdbc.Driver");
		conn = DriverManager.getConnection("jdbc:mysql://localhost/itscreater", "itscreater", "campus123");
		
		if (nation == ""){
			String select = "select * from Post where Continent= ? order by Date desc, Time desc limit ?, ?;";
			pstmt = conn.prepareStatement(select);
			pstmt.setString(1, continent);
			pstmt.setInt(2, starts);
			pstmt.setInt(3, rpps);
			ResultSet rs = pstmt.executeQuery();
			
			while (rs.next()){
				String num = rs.getString("Number");
				String id = rs.getString("ID");
				String writer = rs.getString("Writer");
				String title = rs.getString("Title");
				String content = rs.getString("Content");
				if (title != null) {
					title = title.replace("\n","<br>");
					title = title.replace("\u0020","&nbsp;");
				}
				if (content != null) {
					content = content.replace("\n","<br>");
					content = content.replace("\u0020","&nbsp;");
				}
				String Continent = rs.getString("Continent");
				String Nation = rs.getString("Nation");
				String date = rs.getString("Date");
				String time = rs.getString("Time");
				String hhmm = time.substring(0, 5);
				
				jo.put("Number", num);
				jo.put("ID", id);
				jo.put("WRITER", writer);
				jo.put("TITLE", title);
				jo.put("CONTENT", content);
				jo.put("CONTINENT", Continent);
				jo.put("NATION", Nation);
				jo.put("DATE", date);
				jo.put("TIME", time);
				jo.put("HHMM", hhmm);
				ja.add(jo);
				jo2.put("results", ja);
			}
			String select2 = "select count(*) Total from Post where Continent= ?;";
			pstmt = conn.prepareStatement(select2);
			pstmt.setString(1, continent);
			ResultSet rs2 = pstmt.executeQuery();
			
			while(rs2.next()){
				int total = rs2.getInt("Total");
				jo2.put("total", new Integer(total));
			}
			out.print(jo2);
		} else {
				String select = "select * from Post where Continent=? and Nation=? order by Date desc, Time desc limit ?, ?;";
				pstmt = conn.prepareStatement(select);
				pstmt.setString(1, continent);
				pstmt.setString(2, nation);
				pstmt.setInt(3, starts);
				pstmt.setInt(4, rpps);
				ResultSet rs = pstmt.executeQuery();
				
				while (rs.next()){
					String num = rs.getString("Number");
					String id = rs.getString("ID");
					String writer = rs.getString("Writer");
					String title = rs.getString("Title");
					String content = rs.getString("Content");
					title = title.replace("\n","<br>");
					title = title.replace("\u0020","&nbsp;");
					content = content.replace("\n","<br>");
					content = content.replace("\u0020","&nbsp;");
					String Continent = rs.getString("Continent");
					String Nation = rs.getString("Nation");
					String date = rs.getString("Date");
					String time = rs.getString("Time");
					String hhmm = time.substring(0, 5);
					
					jo.put("Number", num);
					jo.put("ID", id);
					jo.put("WRITER", writer);
					jo.put("TITLE", title);
					jo.put("CONTENT", content);
					jo.put("CONTINENT", Continent);
					jo.put("NATION", Nation);
					jo.put("DATE", date);
					jo.put("TIME", time);
					jo.put("HHMM", hhmm);
					ja.add(jo);	
					jo2.put("results", ja);
				}
				
				String select2 = "select count(*) Total from Post where Continent=? and Nation=?;";
				pstmt = conn.prepareStatement(select2);
				pstmt.setString(1, continent);
				pstmt.setString(2, nation);
				ResultSet rs2 = pstmt.executeQuery();
				
				while(rs2.next()){
					int total = rs2.getInt("Total");
					jo2.put("total", new Integer(total));
				}
				out.print(jo2);
		}
		
	}
	finally{
		try{
			pstmt.close();
		}
		catch (Exception ignored){
		}
		try{
			conn.close();
		}
		catch (Exception ignored){
		}
	}
%>);